# -*- encoding: utf-8 -*-
#import odoo.addons.decimal_precision as dp
from odoo import models, fields, api, _
from odoo.addons import base_cw
from odoo.exceptions import UserError
from odoo.tools.float_utils import float_round, float_compare


class account_detail_summary_wizard(models.Model):
    _name = 'account.detail.summary.wizard'
    _description = u'日记帐统计查询'

    name = fields.Char(default='日记帐统计查询', string='日记帐统计查询')
    date_type = fields.Selection([('this_year', u'本年'),
                                  ('this_month', u'本月'),
                                  ('today', u'今天'),
                                  ('other_month', u'其它期别')], u'日期范围', default='other_month')
    period_id = fields.Many2one('account.period', u'期别')
    start_date = fields.Date(u'起始日')
    end_date = fields.Date(u'截止日')
    from_account_id = fields.Many2one('cncw.account', u'起始会科')
    to_account_id = fields.Many2one('cncw.account', u'截止会科')
    line_ids = fields.One2many('account.detail.summary', 'master_id', u'明细')

    @api.onchange('date_type')
    def onchange_date_type(self):
        if self.date_type == 'this_year':
            year = self.env['account.fiscalyear'].search([('date_start', '<=', fields.Date.context_today(self)),
                                                          ('date_stop', '>=', fields.Date.context_today(self))],
                                                         limit=1)
            if year:
                self.start_date = year.date_start
                self.end_date = year.date_stop

        elif self.date_type == 'this_month':
            period_id = self.env['account.period'].search([('date_start', '<=', fields.Date.context_today(self)),
                                                           ('date_stop', '>=', fields.Date.context_today(self))],
                                                          limit=1)
            if period_id:
                self.start_date = period_id.date_start
                self.end_date = period_id.date_stop
        elif self.date_type == 'today':
            self.start_date = fields.Date.context_today(self)
            self.end_date = fields.Date.context_today(self)
        elif self.date_type == 'other_month':
            pass

    @api.onchange('period_id')
    def onchange_period_id(self):
        if self.period_id:
            self.start_date = self.period_id.date_start
            self.end_date = self.period_id.date_stop

    @api.onchange('from_account_id')
    def onchange_from_account_id(self):
        if self.from_account_id:
            self.to_account_id = self.from_account_id.id


    def action_confirm(self):
        self.ensure_one()
        if not self.start_date:
            raise UserError(_(u'开始日期不可为空!'))

        if not self.end_date:
            raise UserError(_(u'截止日期不可为空!'))

        if not self.from_account_id:
            raise UserError(_(u'开始会科不可为空!'))

        if not self.to_account_id:
            raise UserError(_(u'截止会科不可为空!'))
        sql="""
            delete from account_detail where user_id=%s;
            delete from account_detail_wizard where create_uid=%s and id !=%s;
            select account_detail_summary_settle('%s','%s','%s','%s',%s,%s);
            """ % (self._uid, self._uid, self.id,
            self.start_date, self.end_date, self.from_account_id.code, self.to_account_id.code, self._uid, self.id)
        self._cr.execute(sql)
        self._cr.commit()


class account_detail_summary(models.Model):
    _name = 'account.detail.summary'
    _description = u'日记帐统计查询'

    master_id = fields.Many2one('account.detail.summary.wizard', u'主档',ondelete="cascade")
    sequence = fields.Integer(u'序', default=1)
    account_id = fields.Many2one('cncw.account', u'会科', ondelete="set null")
    begin_amount = fields.Float(u'期初', digits='Amount')
    debit = fields.Float(u'借方金额', digits='Amount')
    credit = fields.Float(u'贷方金额', digits='Amount')
    end_amount = fields.Float(u'期末', digits='Amount')
    debit_count = fields.Integer(u'借方笔数')
    credit_count = fields.Integer(u'贷方笔数')
    uid = fields.Many2one('res.users', u'当前用户', ondelete="set null")
    duration = fields.Char(u'查询期间')

    def init(self):
        self._cr.execute("""
                    CREATE OR REPLACE FUNCTION account_detail_summary_settle(
                            start_date_t timestamp,
                            end_date_t timestamp,
                            from_account_no character varying,
                            to_account_no character varying,
                            uid_t integer,
                            master_id_t integer)
                      RETURNS void AS
                    $BODY$
                    -- 明细分类帐查询
                    -- DROP FUNCTION account_detail_summary_settle(timestamp,timestamp,character varying,character varying,integer,integer)
                    -- select account_detail_summary_settle('2016-09-22','2016-10-30','10011','10011',1,1)

                    declare
                            period_date_start_t timestamp;
                            period_t integer;
                            begin_amount_t numeric:=0.0;
                            debit_cridet_t numeric:=0.0;
                            end_t numeric:=0.0;
                            rec record;
                            rec_account record;
                            seq integer:=0;
                            duration_t character varying;
                    begin


                         select id,date_start into period_t,period_date_start_t from account_period where start_date_t between date_start and date_stop;
                         duration_t:=substring(start_date_t::character varying,1,10)||' ~ '||substring(end_date_t::character varying,1,10);

                         for rec_account in select a.id  from cncw_account a
                         left join subaccount_category b on b.id=a.subaccount_category_id
                         where a.code between from_account_no and to_account_no
                         and a.sub_account_type<>'view' and b.name='现金流项目'
                         loop
                                 seq :=0;
                                 delete from account_detail_summary where uid=uid_t and account_id=rec_account.id;
                                 begin_amount_t:=0.0;
                             debit_cridet_t:=0.0;
                             if start_date_t<>period_date_start_t then
                                 select sum((coalesce(lc_debit,0)-coalesce(lc_credit,0))*(case when c.dc_type='D' then 1 else -1 end)) into debit_cridet_t
                                   from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                               left join cncw_account c on a.account_id=c.id
                                  where b.state in ('draft','confirmed','audited')
                                and period_date_start_t<=b.date and  b.date< start_date_t--不含开始日
                                --and a.sub_account_id is null
                                and a.account_id=rec_account.id;
                              end if;
                              --1.期初
                              select coalesce(begin,0) into  begin_amount_t from account_balance where period_id=period_t and account_id=rec_account.id;
                              begin_amount_t:=coalesce(begin_amount_t,0)+coalesce(debit_cridet_t,0);

                              --insert into account_detail_summary(sequence,account_id,end_amount,uid,duration,create_uid,create_date,master_id)
                              --values(seq,rec_account.id,begin_amount_t,uid_t,duration_t,uid_t,(now() at time zone 'UTC'),master_id_t);

                              --2.查询期间内
                             seq:=seq+1;
                             for rec in select a.account_id,
                                               sum(coalesce(lc_debit,0)) as debit,
                                               sum(coalesce(lc_credit,0)) as credit,
                                               sum(case when a.dc_type='D' then 1 else 0 end) as debit_count,
                                               sum(case when a.dc_type='C' then 1 else 0 end) as credit_count,
                                               sum((coalesce(lc_debit,0)-coalesce(lc_credit,0))*(case when c.dc_type='D' then 1 else -1 end)) as end_amount
                                  from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                  left join cncw_account c on a.account_id=c.id
                                 where b.state in ('draft','confirmed','audited')
                                   and b.date between start_date_t and end_date_t
                                   and a.account_id=rec_account.id
                                 group by a.account_id
                             loop
                               end_t:=coalesce(begin_amount_t,0)+(coalesce(rec.debit,0)-coalesce(rec.credit,0));

                               insert into account_detail_summary(sequence,account_id,begin_amount,debit,credit,end_amount,debit_count,credit_count,uid,duration,create_uid,create_date,master_id)
                               values(seq,rec.account_id,begin_amount_t,rec.debit,rec.credit,end_t,
                               rec.debit_count,rec.credit_count,uid_t,duration_t,uid_t,(now() at time zone 'UTC'),master_id_t);
                             end loop;
                         end loop;
                     end;
                     $BODY$
                      LANGUAGE plpgsql VOLATILE
                      COST 100;
                   """)